﻿/******************************************************************
** 文 件 名: BD_ProjectFileCabinet_DAL.cs
** Copyright (c) 2010-2012 Honestysoft (GuangZhou) Co.,LTD
** 创 建 人: 此代码由生成器自动生成
** 创建日期: 2012-6-23 22:52:16
** 描    述: 实现公共部分的数据访问封装，提供项目文件柜的数据访问类
** 版    本: 2.0.0.0
** 修改描述:
** 修 改 人:
** 修改日期:
******************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Honestysoft.BaseObject;
using Honestysoft.Financing.Model;

namespace Honestysoft.Financing.DataAccess
{
	/// <summary>
    /// BD_ProjectFileCabinet数据访问类
    /// </summary>
	public partial class BD_ProjectFileCabinet_DAL : BaseDataAccess<BD_ProjectFileCabinet>
    {		
		/// <summary>
        /// 构造函数，使用指定的连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        public BD_ProjectFileCabinet_DAL(string connectionStringName)
            : base(connectionStringName)
        {

        }

        /// <summary>
        /// 得到项目文件柜
        /// </summary>
        /// <param name="category"></param>
        /// <param name="sn"></param>
        /// <param name="name"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetProjectFileCabinet(string category, string sn, string name, int pageSize, int currentPageIndex, out int pageCount, out int recordCount)
        {
            DataTable dt = null;
            //插入没有初始化文件柜的项目
            string sql = @"Insert Into BD_ProjectFileCabinet(ID,CabinetNo)
	            Select ID,''
	            From BD_Project Where ID not in
	            (Select ID From BD_ProjectFileCabinet)";
            DbCommand command = GetDbCommand(sql);
            ExecuteNonQuery(command);
            DbParameter pSN = CreateParameter("SN");
            pSN.DbType = DbType.String;
            pSN.Value = sn;
            DbParameter pOwner971 = CreateParameter("ProjectCategory");
            pOwner971.DbType = DbType.String;
            pOwner971.Value = category;
            DbParameter pName = CreateParameter("Name");
            pName.DbType = DbType.String;
            pName.Value = name;
            sql = string.Format(@"Select A.ID,A.Name,
	            Convert(decimal(20,2),Replace(Replace(A.SN,'(','.'),')','')) as vSort, A.SN,B.CompName as Owner971Name,C.CabinetNo,
                Isnull(D.ProjectFileCount,0) as ProjectFileCount
	            From BD_Project A Inner Join Org_Company B On A.Owner971=B.ID
	            Inner Join BD_ProjectFileCabinet C ON A.ID=C.ID
	            Left Join
		            (Select DataID,Count(ID) As ProjectFileCount
		            From Sys_Attachment
		            Where AttachmentBuType in (" + "'" + ((int)EnumItem.ProjectDocument.Doc1).ToString() + "','" + ((int)EnumItem.ProjectDocument.Doc2).ToString() + "','" +
                   ((int)EnumItem.ProjectDocument.Doc3).ToString() + "','" + ((int)EnumItem.ProjectDocument.Doc4).ToString() + "','" +
                   ((int)EnumItem.ProjectDocument.Doc5).ToString() + "','" + ((int)EnumItem.ProjectDocument.Doc5).ToString() + "','" +
                   ((int)EnumItem.ProjectDocument.Doc7).ToString() + "','" + ((int)EnumItem.ProjectDocument.Doc8).ToString() + "','" +
                   ((int)EnumItem.ProjectDocument.Doc9).ToString() + @"')
		            Group By DataID) D
	            On A.ID=D.DataID Where 1=1 {0} {1} {2}",
                   string.IsNullOrEmpty(category) ? "" : string.Format(" And A.ProjectCategory={0}", pOwner971.ParameterName),
                   string.IsNullOrEmpty(sn) ? "" : string.Format(" And A.SN LIKE '%'+{0}+ '%' ", pSN.ParameterName),
                   string.IsNullOrEmpty(name) ? "" : string.Format(" And A.Name LIKE '%'+{0}+ '%' ", pName.ParameterName));
            command = GetDbCommand(sql);
            if (!string.IsNullOrEmpty(sn))
            {
                command.Parameters.Add(pSN);
            }
            if (!string.IsNullOrEmpty(category))
            {
                command.Parameters.Add(pOwner971);
            }
            if (!string.IsNullOrEmpty(name))
            {
                command.Parameters.Add(pName);
            }
            dt = ExecuteDataTable(command, "vSort", pageSize, currentPageIndex, out pageCount, out recordCount);
            return dt;
        }

        /// <summary>
        /// 得到合同文件柜
        /// </summary>
        /// <param name="projectCategory"></param>
        /// <param name="projectSN"></param>
        /// <param name="projectName"></param>
        /// <param name="contractName"></param>
        /// <param name="pageSize"></param>
        /// <param name="currentPageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataTable GetContractFileCabinet(string projectCategory, string projectSN, string projectName,string contractName, int pageSize, int currentPageIndex, out int pageCount, out int recordCount)
        {
            DataTable dt = null;
            DbTransaction transaction = GetDbTransaction();
            DbConnection connection = transaction.Connection;
            try
            {
                //插入没有初始化文件柜的项目
                string sql = @"Insert Into BD_ProjectFileCabinet(ID,CabinetNo)
	            Select ID,''
	            From BD_Project Where ID not in
	            (Select ID From BD_ProjectFileCabinet)";
                DbCommand command = GetDbCommand(sql);
                ExecuteNonQuery(command, transaction);
                DbParameter pProjectSN = CreateParameter("SN");
                pProjectSN.DbType = DbType.String;
                pProjectSN.Value = projectSN;
                DbParameter pOwner971 = CreateParameter("ProjectCategory");
                pOwner971.DbType = DbType.String;
                pOwner971.Value = projectCategory;
                DbParameter pProjectName = CreateParameter("Name");
                pProjectName.DbType = DbType.String;
                pProjectName.Value = projectName;
                DbParameter pContractName = CreateParameter("Name");
                pContractName.DbType = DbType.String;
                pContractName.Value = contractName;
                sql = string.Format(@"Select A.ID as ProjectID,A.Name as ProjectName,
                            Convert(decimal(20,2),Replace(Replace(A.SN,'(','.'),')','')) as vSort, 
                            A.SN as ProjectSN,B.CompName as Owner971Name,C.CabinetNo,
                            E.Name as ContractName,E.ID,E.Code,
                            Isnull(D.ContractFileCount,0) as ContractFileCount
                            From BD_Project A Inner Join Org_Company B On A.Owner971=B.ID
                            Inner Join BD_ProjectFileCabinet C ON A.ID=C.ID
                            Inner Join BD_Contract E On E.ProjectID=A.ID
                            Left Join
                                (Select DataID,Count(ID) As ContractFileCount
                                From Sys_Attachment
                                Where AttachmentBuType in ('110701','110702','110703','110704','110705','110706','110707','110708','110709')
                                Group By DataID) D
                            On E.ID=D.DataID  Where 1=1 {0} {1} {2} {3}",
                       string.IsNullOrEmpty(projectCategory) ? "" : string.Format(" And A.ProjectCategory={0}", pOwner971.ParameterName),
                       string.IsNullOrEmpty(projectSN) ? "" : string.Format(" And A.SN LIKE '%'+{0}+ '%' ", pProjectSN.ParameterName),
                       string.IsNullOrEmpty(projectName) ? "" : string.Format(" And A.Name LIKE '%'+{0}+ '%' ", pProjectName.ParameterName),
                       string.IsNullOrEmpty(contractName) ? "" : string.Format(" And E.Name LIKE '%'+{0}+ '%' ",pContractName.ParameterName));
                command = GetDbCommand(sql);
                if (!string.IsNullOrEmpty(projectSN))
                {
                    command.Parameters.Add(pProjectSN);
                }
                if (!string.IsNullOrEmpty(projectCategory))
                {
                    command.Parameters.Add(pOwner971);
                }
                if (!string.IsNullOrEmpty(projectName))
                {
                    command.Parameters.Add(pProjectName);
                }
                if (!string.IsNullOrEmpty(contractName))
                {
                    command.Parameters.Add(pContractName);
                }
                dt = ExecuteDataTable(command, "vSort,Code", pageSize, currentPageIndex, out pageCount, out recordCount, transaction);
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return dt;
        }

        public DataTable GetFileCabinetTree(string parentId)
        {
            DbParameter pParentID = CreateParameter("ParentID");
            pParentID.DbType = DbType.String;
            pParentID.Value = parentId;
            string sql = string.Format(@"Select A.ID,A.Code,A.Name,A.vName,A.ParentID,A.NodeType,Count(B.ID) as ChildNodeCount 
                From V_ProjectContractFileCabinet A
                Left Join V_ProjectContractFileCabinet B
                On A.ID=B.ParentID
                Where {0}
                Group by A.ID,A.Code,A.Name,A.vName,A.ParentID,A.NodeType
                Order by A.ParentID,A.Code", string.IsNullOrEmpty(parentId) ? " A.ParentID IS NULL " : string.Format("A.ParentID={0}", pParentID.ParameterName));
            DbCommand command = GetDbCommand(sql);
            if (!string.IsNullOrEmpty(parentId))
            {
                command.Parameters.Add(pParentID);
            }
            return ExecuteDataTable(command);
        }
    }
}
